﻿<cfsilent>
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		downFile = URLEncodedFormat( "学期开课目录", "utf-8");
		
		termId = event.getArg("TID");
		
		/* 学期开课目录 */
		sql = "SELECT 
					'P' task_type, 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer, 
					sum(a.task_chooesd) stu_count, count(*) task_count 
				FROM t_task a 
					INNER JOIN t_course b ON b.cid = a.cid
					INNER JOIN t_institute c ON b.institute_id = c.institute_id
				WHERE 
					a.term_id = :termId 
					AND substr(a.task_prop, 7, 1) = '0' 
				GROUP BY 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer 
				UNION ALL 
				SELECT 
					'C' task_type, 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer, 
					sum(a.task_chooesd) stu_count, count(*) task_count 
				FROM t_common_task a 
					INNER JOIN t_course b ON b.cid = a.cid
					INNER JOIN t_institute c ON b.institute_id = c.institute_id
				WHERE 
					a.term_id = :termId 
					AND substr(a.task_prop, 7, 1) = '0' 
				GROUP BY 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer 
				UNION ALL 
				SELECT 
					'T' task_type, 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer, 
					sum(a.task_chooesd) stu_count, count(*) task_count 
				FROM t_sport_task a 
					INNER JOIN t_course b ON b.cid = a.cid
					INNER JOIN t_institute c ON b.institute_id = c.institute_id
				WHERE 
					a.term_id = :termId 
					AND substr(a.task_prop, 7, 1) = '0' 
				GROUP BY 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer 
				UNION ALL 
				SELECT 
					'R' task_type, 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer, 
					sum(a.task_chooesd) stu_count, count(*) task_count 
				FROM t_reopen_task a 
					INNER JOIN t_course b ON b.cid = a.cid
					INNER JOIN t_institute c ON b.institute_id = c.institute_id
				WHERE 
					a.term_id = :termId 
					AND substr(a.task_prop, 7, 1) = '0' 
				GROUP BY 
					c.institute_name, 
					b.course_code, b.course_name, b.course_credit, b.period, b.period_theory, b.period_practice, b.period_computer 
				ORDER BY task_type, course_name";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );

		rs_course = queryObj.execute( sql=sql ).getResult();
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("开课目录", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "开课类别,任课单位,课程号,课程,学分,学时,理论学时,实践学时,上机学时,选课人数,开班数量");

		row = 1;
		
		/* 写入开课目录 */
		for ( a=1; a LTE rs_course.recordCount; a++ ) {
			
			row++;
			
			rowData = dictAdvice.getTaskType( rs_course["task_type"][a] ) 
					& "," & rs_course["institute_name"][a]
					& "," & rs_course["course_code"][a]
					& "," & rs_course["course_name"][a]
					& "," & numberFormat( rs_course["course_credit"][a], "_.__" )
					& "," & rs_course["period"][a]
					& "," & rs_course["period_theory"][a]
					& "," & rs_course["period_practice"][a]
					& "," & rs_course["period_computer"][a]
					& "," & rs_course["stu_count"][a]
					& "," & rs_course["task_count"][a];
					
			excel.SpreadsheetAddrow(spreadsheetObj, rowData); 
			
		}
	
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>